
from typing import List, Optional, Any, Union
from datetime import datetime, date
import pymysql
from ..models.{{ class_name }} import {{ class_name }}

class {{ mapper_class_name }}:
    """
    {{ table_name }} 表的数据库操作类
    """

    def __init__(self, connection: pymysql.connections.Connection):
        """
        初始化{{ mapper_class_name }}

        Args:
            connection: 数据库连接对象
        """
        self.connection = connection

    def insert(self, entity: {{ class_name }}) -> int:
        """
        插入一条记录

        Args:
            entity: 要插入的{{ class_name }}对象

        Returns:
            插入成功的记录ID
        """
        # 构建字段名和占位符
        field_names = []
        placeholders = []
        params = []

        {% for field in fields %}
        {% if not field.is_primary %}
        field_names.append("`{{ field.name }}`")
        placeholders.append("%s")
        params.append(entity.{{ field.name }})
        {% endif %}
        {% endfor %}

        sql = f"""
        INSERT INTO {{ table_name }} ({", ".join(field_names)})
        VALUES ({", ".join(placeholders)})
        """

        with self.connection.cursor() as cursor:
            cursor.execute(sql, params)
            self.connection.commit()
            return cursor.lastrowid

    def update(self, entity: {{ class_name }}) -> int:
        """
        更新一条记录

        Args:
            entity: 要更新的{{ class_name }}对象

        Returns:
            更新的记录数
        """
        # 构建SET子句
        set_clauses = []
        params = []

        {% for field in fields %}
        {% if not field.is_primary %}
        set_clauses.append(f"`{{ field.name }}` = %s")
        params.append(entity.{{ field.name }})
        {% endif %}
        {% endfor %}

        # 构建WHERE子句
        where_clauses = []

        {% for field in fields %}
        {% if field.is_primary %}
        where_clauses.append(f"`{{ field.name }}` = %s")
        params.append(entity.{{ field.name }})
        {% endif %}
        {% endfor %}

        sql = f"""
        UPDATE {{ table_name }} SET
        {", ".join(set_clauses)}
        WHERE {" AND ".join(where_clauses)}
        """

        with self.connection.cursor() as cursor:
            cursor.execute(sql, params)
            self.connection.commit()
            return cursor.rowcount

    def delete(self, {% for field in fields %}{% if field.is_primary %}{{ field.name }}: {{ field.type }}{% if not loop.last %}, {% endif %}{% endif %}{% endfor %}) -> int:
        """
        删除一条记录

        Args:
            {% for field in fields %}{% if field.is_primary %}{{ field.name }}: 主键值{% endif %}{% endfor %}

        Returns:
            删除的记录数
        """
        # 构建WHERE子句
        where_clauses = []
        params = []

        {% for field in fields %}
        {% if field.is_primary %}
        where_clauses.append(f"`{{ field.name }}` = %s")
        params.append({{ field.name }})
        {% endif %}
        {% endfor %}

        sql = f"""
        DELETE FROM {{ table_name }}
        WHERE {" AND ".join(where_clauses)}
        """

        with self.connection.cursor() as cursor:
            cursor.execute(sql, params)
            self.connection.commit()
            return cursor.rowcount

    def get_by_id(self, {% for field in fields %}{% if field.is_primary %}{{ field.name }}: {{ field.type }}{% if not loop.last %}, {% endif %}{% endif %}{% endfor %}) -> Optional[{{ class_name }}]:
        """
        根据ID查询记录

        Args:
            {% for field in fields %}{% if field.is_primary %}{{ field.name }}: 主键值{% endif %}{% endfor %}

        Returns:
            查询到的{{ class_name }}对象，如果不存在则返回None
        """
        # 构建WHERE子句
        where_clauses = []
        params = []

        {% for field in fields %}
        {% if field.is_primary %}
        where_clauses.append(f"`{{ field.name }}` = %s")
        params.append({{ field.name }})
        {% endif %}
        {% endfor %}

        sql = f"""
        SELECT * FROM {{ table_name }}
        WHERE {" AND ".join(where_clauses)}
        """

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, params)
            result = cursor.fetchone()

            if result:
                return {{ class_name }}.from_dict(result)
            return None

    def find_all(self, limit: int = None, offset: int = None) -> List[{{ class_name }}]:
        """
        查询所有记录

        Args:
            limit: 限制返回的记录数
            offset: 偏移量

        Returns:
            查询到的{{ model_class_name }}对象列表
        """
        sql = "SELECT * FROM {{ table_name }}"

        if limit is not None:
            sql += f" LIMIT {limit}"
            if offset is not None:
                sql += f" OFFSET {offset}"

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql)
            results = cursor.fetchall()

            return [{{ class_name }}.from_dict(result) for result in results]

    def find_by_condition(self, conditions: dict, limit: int = None, offset: int = None) -> List[{{ class_name }}]:
        """
        根据条件查询记录

        Args:
            conditions: 查询条件字典
            limit: 限制返回的记录数
            offset: 偏移量

        Returns:
            查询到的{{ model_class_name }}对象列表
        """
        sql = "SELECT * FROM {{ table_name }} WHERE "

        where_clauses = []
        params = []

        for field, value in conditions.items():
            where_clauses.append(f"`{field}` = %s")
            params.append(value)

        sql += " AND ".join(where_clauses)

        if limit is not None:
            sql += f" LIMIT {limit}"
            if offset is not None:
                sql += f" OFFSET {offset}"

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, params)
            results = cursor.fetchall()

            return [{{ class_name }}.from_dict(result) for result in results]

    def count_by_condition(self, conditions: dict) -> int:
        """
        根据条件统计记录数

        Args:
            conditions: 查询条件字典

        Returns:
            记录数
        """
        sql = "SELECT COUNT(*) as count FROM {{ table_name }} WHERE "

        where_clauses = []
        params = []

        for field, value in conditions.items():
            where_clauses.append(f"`{field}` = %s")
            params.append(value)

        sql += " AND ".join(where_clauses)

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, params)
            result = cursor.fetchone()

            return result['count']

    def count_all(self) -> int:
        """
        统计所有记录数

        Returns:
            记录数
        """
        sql = "SELECT COUNT(*) as count FROM {{ table_name }}"

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()

            return result['count']

    def batch_insert(self, entities: List[{{ class_name }}]) -> List[int]:
        """
        批量插入记录

        Args:
            entities: 要插入的{{ class_name }}对象列表

        Returns:
            插入成功的记录ID列表
        """
        if not entities:
            return []

        # 构建字段名和占位符
        field_names = []
        placeholders = []

        {% for field in fields %}
        {% if not field.is_primary %}
        field_names.append("`{{ field.name }}`")
        {% endif %}
        {% endfor %}

        # 为每个实体构建参数集
        params = []
        for entity in entities:
            param_set = []

            {% for field in fields %}
            {% if not field.is_primary %}
            param_set.append(entity.{{ field.name }})
            {% endif %}
            {% endfor %}

            params.append(param_set)

        sql = f"""
        INSERT INTO {{ table_name }} ({", ".join(field_names)})
        VALUES ({", ".join(["%s"] * len(field_names))})
        """

        ids = []
        with self.connection.cursor() as cursor:
            for param_set in params:
                cursor.execute(sql, param_set)
                ids.append(cursor.lastrowid)
            self.connection.commit()
            return ids

    def batch_delete(self, ids: List[int]) -> int:
        """
        批量删除记录

        Args:
            ids: 要删除的记录ID列表

        Returns:
            删除的记录数
        """
        if not ids:
            return 0

        # 构建WHERE子句 - 假设只有一个主键
        primary_key_name = ""
        {% for field in fields %}
        {% if field.is_primary %}
        primary_key_name = "{{ field.name }}"
        {% endif %}
        {% endfor %}

        sql = f"""
        DELETE FROM {{ table_name }}
        WHERE `{primary_key_name}` IN ({", ".join(["%s"] * len(ids))})
        """

        params = ids

        with self.connection.cursor() as cursor:
            cursor.execute(sql, params)
            self.connection.commit()
            return cursor.rowcount

    def get_by_ids(self, ids: List[int]) -> List[{{ class_name }}]:
        """
        根据ID列表查询记录

        Args:
            ids: 主键值列表

        Returns:
            查询到的{{ class_name }}对象列表
        """
        if not ids:
            return []

        # 构建WHERE子句 - 假设只有一个主键
        primary_key_name = ""
        {% for field in fields %}
        {% if field.is_primary %}
        primary_key_name = "{{ field.name }}"
        {% endif %}
        {% endfor %}

        sql = f"""
        SELECT * FROM {{ table_name }}
        WHERE `{primary_key_name}` IN ({", ".join(["%s"] * len(ids))})
        """

        params = ids

        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, params)
            results = cursor.fetchall()

            return [{{ class_name }}.from_dict(result) for result in results]
